# 社区dao层
from dao import db


# 按照要求返回社区页面的帖子
def dao_community_default(pagination, amount, rule):
    # 筛选规则，1 代表按点赞排序，2 代表按时间排序，3 代表按浏览量排序
    if pagination == 1:
        start_limit = 0
    else:
        start_limit = amount * (pagination - 1)

    if rule == 1:
        sql_1 = "select articleId, uuid, articleUploadDate, browse, title, descript, coverUrl, likes, commits, " \
                "userName from communityArticle order by likes desc limit %d, %d" % (start_limit, amount)
        data_1 = db.session.execute(sql_1).fetchall()
        if data_1 is None:
            return False
        else:
            result = []
            field_li_1 = ['articleId', 'uuid', 'uploadDate', 'browse', 'title', 'description', 'coverUrl', 'like',
                          'commit', 'userName']
            for data_1_i in data_1:
                temp_1 = dict(zip(field_li_1, data_1_i))
                temp_1['articleId'] = str(temp_1['articleId'])
                temp_1['uuid'] = str(temp_1['uuid'])
                temp_1['uploadDate'] = str(temp_1['uploadDate'])
                result.append(temp_1)
            return result
    elif rule == 2:
        sql_2 = "select articleId, uuid, articleUploadDate, browse, title, descript, coverUrl, likes, commits, " \
                "userName from communityArticle order by articleUploadDate limit %d, %d" % (start_limit, amount)
        data_2 = db.session.execute(sql_2).fetchall()
        if data_2 is None:
            return False
        else:
            result = []
            field_li_2 = ['articleId', 'uuid', 'uploadDate', 'browse', 'title', 'description', 'coverUrl', 'like',
                          'commit', 'userName']
            for data_2_i in data_2:
                temp_2 = dict(zip(field_li_2, data_2_i))
                temp_2['articleId'] = str(temp_2['articleId'])
                temp_2['uuid'] = str(temp_2['uuid'])
                temp_2['uploadDate'] = str(temp_2['uploadDate'])
                result.append(temp_2)
            return result
    elif rule == 3:
        sql_3 = "select articleId, uuid, articleUploadDate, browse, title, descript, coverUrl, likes, commits, " \
                "userName from communityArticle order by browse desc limit %d, %d" % (start_limit, amount)
        data_3 = db.session.execute(sql_3).fetchall()
        if data_3 is None:
            return False
        else:
            result = []
            field_li_3 = ['articleId', 'uuid', 'uploadDate', 'browse', 'title', 'description', 'coverUrl', 'like',
                          'commit', 'userName']
            for data_3_i in data_3:
                temp_3 = dict(zip(field_li_3, data_3_i))
                temp_3['articleId'] = str(temp_3['articleId'])
                temp_3['uuid'] = str(temp_3['uuid'])
                temp_3['uploadDate'] = str(temp_3['uploadDate'])
                result.append(temp_3)
            return result
    else:
        return False


# 查询是否点赞该文章
def dao_community_isLike_article(articleId, uuid):
    sql = "select * from articleLike where articleId = %d and uuid = %d" % (articleId, uuid)
    data = db.session.execute(sql).fetchone()
    if data is not None:
        return True
    else:
        return False


# 查询是否点赞该评论
def dao_community_isLike_comment(commitId, uuid):
    sql = "select * from commentsLike where commitId = %d and uuid = %d" % (commitId, uuid)
    data = db.session.execute(sql).fetchone()
    if data is not None:
        return True
    else:
        return False


# 查询是否点赞该回复
def dao_community_isLike_reply(replyId, uuid):
    sql = "select * from replysLike where replyId = %d and uuid = %d" % (replyId, uuid)
    data = db.session.execute(sql).fetchone()
    if data is not None:
        return True
    else:
        return False


# 查询用户名和头像
def dao_community_search_userName_and_avatar(uuid):
    sql = "select userName, avatar from user where uuid = %d" % uuid
    data = db.session.execute(sql).fetchone()
    if data is None:
        return False
    else:
        field_li = ['userName', 'avatar']
        result = dict(zip(field_li, data))
        return result


# 数据库查询回复
def dao_community_searchReplys(commitId, uuid):
    sql = "select commitId, replyId, uuid, replyContent, replyDate, likes" \
          " from communityReplys where commitId = %d" % commitId
    data = db.session.execute(sql).fetchall()
    if data is None:
        return False
    else:
        result = []
        field_li_1 = ['commitId', 'replyId', 'uuid', 'content', 'date', 'like']
        for data_i in data:
            temp_1 = dict(zip(field_li_1, data_i))
            temp_2 = dao_community_search_userName_and_avatar(temp_1['uuid'])
            temp_1['userName'] = temp_2['userName']
            temp_1['avatar'] = temp_2['avatar']
            temp_1['isLike'] = dao_community_isLike_reply(temp_1['replyId'], uuid)
            temp_1['commitId'] = str(temp_1['commitId'])
            temp_1['replyId'] = str(temp_1['replyId'])
            temp_1['uuid'] = str(temp_1['uuid'])
            temp_1['date'] = str(temp_1['date'])
            result.append(temp_1)
        return result


# 数据库查询评论
def dao_community_searchComments(articleId, uuid):
    sql = "select commitId, uuid, commitContent, commitDate, likes" \
          " from communityComments where articleId = %d" % articleId
    data = db.session.execute(sql).fetchall()
    if data is None:
        return False
    else:
        result = []
        field_li_1 = ['commitId', 'uuid', 'content', 'date', 'like']
        for data_i in data:
            temp_1 = dict(zip(field_li_1, data_i))
            temp_2 = dao_community_search_userName_and_avatar(temp_1['uuid'])
            temp_1['userName'] = temp_2['userName']
            temp_1['avatar'] = temp_2['avatar']
            temp_1['isLike'] = dao_community_isLike_comment(temp_1['commitId'], uuid)
            temp_1['replys'] = dao_community_searchReplys(temp_1['commitId'], uuid)
            temp_1['commitId'] = str(temp_1['commitId'])
            temp_1['uuid'] = str(temp_1['uuid'])
            temp_1['date'] = str(temp_1['date'])
            result.append(temp_1)
        return result


# 数据库查询文章详情
def dao_community_detail(articleId, uuid):
    # 此处拿到id，浏览量+1
    sql_1_1 = "select browse from communityArticle where articleId = %d" % articleId
    data_1 = db.session.execute(sql_1_1).fetchone()
    if data_1 is None:
        return False
    else:
        field_li_1 = ['browse']
        browse_dic = dict(zip(field_li_1, data_1))
        browse = browse_dic['browse'] + 1

    sql_1_2 = "update communityArticle set browse = %d where articleId = %d" % (browse, articleId)
    try:
        db.session.execute(sql_1_2)
        db.session.commit()
    except Exception as e:
        db.session.rollback()

    sql_2 = "select title, articleUploadDate, browse, likes, commits, articleContent" \
            " from communityArticle where articleId = %d" % articleId
    data_2 = db.session.execute(sql_2).fetchone()
    if data_2 is None:
        return False
    else:
        field_li_1 = ['title', 'articleUploadDate', 'browse', 'like', 'commit', 'articleContent']
        result = dict(zip(field_li_1, data_2))
        result['articleUploadDate'] = str(result['articleUploadDate'])
        result['isLike'] = dao_community_isLike_article(articleId, uuid)
        result['commits'] = dao_community_searchComments(articleId, uuid)
        return result


# 数据库中加入新评论
def dao_community_sendCommit(articleId, content, uuid):
    sql_1 = "select max(commitId) from communityComments"
    data_1 = db.session.execute(sql_1).fetchone()
    if data_1 is not None:
        field_li_1 = ['commitId']
        cId = dict(zip(field_li_1, data_1))
        commitId = {'commitId': cId['commitId'] + 1}
    else:
        commitId = {'commitId': 100000}

    sql_2 = "insert into communityComments (commitId, uuid, commitContent, articleId, commitDate)" \
            " values (%d, %d, '%s', %d, now())" % (commitId['commitId'], uuid, content, articleId)
    try:
        db.session.execute(sql_2)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        return False
    else:
        # 增加文章评论量
        sql_2_1 = "select commits from communityArticle where articleId = %d" % articleId
        data_2_1 = db.session.execute(sql_2_1).fetchone()
        field_li_2_1 = ['commits']
        commits_dic = dict(zip(field_li_2_1, data_2_1))
        commits = commits_dic['commits'] + 1

        sql_2_2 = "update communityArticle set commits = %d where articleId = %d" % (commits, articleId)
        try:
            db.session.execute(sql_2_2)
            db.session.commit()
        except Exception as e:
            db.session.rollback()

        return True


# 数据库更新文章点赞数据
def dao_community_articleLike(articleId, like, uuid):
    # true点赞，false取消点赞
    sql_1 = "select * from articleLike where articleId = %d and uuid = %d" % (articleId, uuid)
    data_1 = db.session.execute(sql_1).fetchone()
    if data_1 is not None:
        isLike = True
    else:
        isLike = False

    if like:
        if isLike:
            return False
        else:
            sql_2 = "insert into articleLike (articleId, uuid) values (%d, %d)" % (articleId, uuid)
            try:
                db.session.execute(sql_2)
                db.session.commit()
            except Exception as e:
                db.session.rollback()
                return False
            else:
                # 更新文章点赞量
                sql_2_1 = "select likes from communityArticle where articleId = %d" % articleId
                data_2_1 = db.session.execute(sql_2_1).fetchone()
                field_li_2_1 = ['likes']
                like_dic_1 = dict(zip(field_li_2_1, data_2_1))
                likes_1 = like_dic_1['likes'] + 1

                sql_2_2 = "update communityArticle set likes = %d where articleId = %d" % (likes_1, articleId)
                try:
                    db.session.execute(sql_2_2)
                    db.session.commit()
                except Exception as e:
                    db.session.rollback()
                return True
    else:
        if isLike:
            sql_3 = "delete from articleLike where  articleId = %d and uuid = %d" % (articleId, uuid)
            try:
                db.session.execute(sql_3)
                db.session.commit()
            except Exception as e:
                db.session.rollback()
                return False
            else:
                # 更新文章点赞量
                sql_3_1 = "select likes from communityArticle where articleId = %d" % articleId
                data_3_1 = db.session.execute(sql_3_1).fetchone()
                field_li_3_1 = ['likes']
                like_dic_2 = dict(zip(field_li_3_1, data_3_1))
                likes_2 = like_dic_2['likes'] - 1

                sql_3_2 = "update communityArticle set likes = %d where articleId = %d" % (likes_2, articleId)
                try:
                    db.session.execute(sql_3_2)
                    db.session.commit()
                except Exception as e:
                    db.session.rollback()
                return True
        else:
            return False


# 数据库更新评论点赞数据
def dao_community_commitLike(commitId, like, uuid):
    # true点赞，false取消点赞
    sql_1 = "select * from commentsLike where commitId = %d and uuid = %d" % (commitId, uuid)
    data_1 = db.session.execute(sql_1).fetchone()
    if data_1 is not None:
        isLike = True
    else:
        isLike = False

    if like:
        if isLike:
            return False
        else:
            sql_2 = "insert into commentsLike (commitId, uuid) values (%d, %d)" % (commitId, uuid)
            try:
                db.session.execute(sql_2)
                db.session.commit()
            except Exception as e:
                db.session.rollback()
                return False
            else:
                # 更新评论点赞量
                sql_2_1 = "select likes from communityComments where commitId = %d" % commitId
                data_2_1 = db.session.execute(sql_2_1).fetchone()
                field_li_2_1 = ['likes']
                like_dic_1 = dict(zip(field_li_2_1, data_2_1))
                likes_1 = like_dic_1['likes'] + 1

                sql_2_2 = "update communityComments set likes = %d where commitId = %d" % (likes_1, commitId)
                try:
                    db.session.execute(sql_2_2)
                    db.session.commit()
                except Exception as e:
                    db.session.rollback()
                return True
    else:
        if isLike:
            sql_3 = "delete from commentsLike where  commitId = %d and uuid = %d" % (commitId, uuid)
            try:
                db.session.execute(sql_3)
                db.session.commit()
            except Exception as e:
                db.session.rollback()
                return False
            else:
                # 更新评论点赞量
                sql_3_1 = "select likes from communityComments where commitId = %d" % commitId
                data_3_1 = db.session.execute(sql_3_1).fetchone()
                field_li_3_1 = ['likes']
                like_dic_2 = dict(zip(field_li_3_1, data_3_1))
                likes_2 = like_dic_2['likes'] - 1

                sql_3_2 = "update communityComments set likes = %d where commitId = %d" % (likes_2, commitId)
                try:
                    db.session.execute(sql_3_2)
                    db.session.commit()
                except Exception as e:
                    db.session.rollback()
                return True
        else:
            return False


# 数据库更新回复点赞数据
def dao_community_replyLike(replyId, like, uuid):
    # true点赞，false取消点赞
    sql_1 = "select * from replysLike where replyId = %d and uuid = %d" % (replyId, uuid)
    data_1 = db.session.execute(sql_1).fetchone()
    if data_1 is not None:
        isLike = True
    else:
        isLike = False

    if like:
        if isLike:
            return False
        else:
            sql_2 = "insert into replysLike (replyId, uuid) values (%d, %d)" % (replyId, uuid)
            try:
                db.session.execute(sql_2)
                db.session.commit()
            except Exception as e:
                db.session.rollback()
                return False
            else:
                # 更新回复点赞量
                sql_2_1 = "select likes from communityReplys where replyId = %d" % replyId
                data_2_1 = db.session.execute(sql_2_1).fetchone()
                field_li_2_1 = ['likes']
                like_dic_1 = dict(zip(field_li_2_1, data_2_1))
                likes_1 = like_dic_1['likes'] + 1

                sql_2_2 = "update communityReplys set likes = %d where replyId = %d" % (likes_1, replyId)
                try:
                    db.session.execute(sql_2_2)
                    db.session.commit()
                except Exception as e:
                    db.session.rollback()
                return True
    else:
        if isLike:
            sql_3 = "delete from replysLike where  replyId = %d and uuid = %d" % (replyId, uuid)
            try:
                db.session.execute(sql_3)
                db.session.commit()
            except Exception as e:
                db.session.rollback()
                return False
            else:
                # 更新回复点赞量
                sql_3_1 = "select likes from communityReplys where replyId = %d" % replyId
                data_3_1 = db.session.execute(sql_3_1).fetchone()
                field_li_3_1 = ['likes']
                like_dic_2 = dict(zip(field_li_3_1, data_3_1))
                likes_2 = like_dic_2['likes'] - 1

                sql_3_2 = "update communityReplys set likes = %d where replyId = %d" % (likes_2, replyId)
                try:
                    db.session.execute(sql_3_2)
                    db.session.commit()
                except Exception as e:
                    db.session.rollback()
                return True
        else:
            return False


# 数据库新增评论回复数据
def dao_community_reply(commitId, uuid, content):
    sql_1 = "select max(replyId) from communityReplys"
    data_1 = db.session.execute(sql_1).fetchone()
    if data_1 is not None:
        field_li_1 = ['replyId']
        rId = dict(zip(field_li_1, data_1))
        replyId = {'replyId': rId['replyId'] + 1}
    else:
        replyId = {'replyId': 100000}

    sql_2 = "insert into communityReplys (replyId, uuid, replyContent, commitId, replyDate)" \
            " values (%d, %d, '%s', %d, now())" % (replyId['replyId'], uuid, content, commitId)
    try:
        db.session.execute(sql_2)
        db.session.commit()
    except Exception as e:
        db.session.rollback()
        return False
    else:
        return True
